CREATE OR REPLACE PACKAGE LotSchedule IS

--===============================================================
-- Package information
--===============================================================
FUNCTION Version RETURN VARCHAR2
;
--===============================================================
-- Type declarations
--===============================================================

--===============================================================
-- Function and Procedure declarations
--===============================================================
FUNCTION GetLotIds (ProductName IN VARCHAR2
                    , ProcessSpecName IN VARCHAR2
                    , ProcessSpecRevision IN VARCHAR2
                    , ProcessSpecObjectCategory IN VARCHAR2
                    , ProcessSpecObjectType IN VARCHAR2
                    , SchedulingRuleName IN VARCHAR2
                    , ScheduleQty IN VARCHAR2
                    , TFI IN VARCHAR2
                    , FlowCode IN VARCHAR2
                    , ConCode IN VARCHAR2
                    , Priority IN VARCHAR2)
RETURN VARCHAR2
;
PROCEDURE GetLotIds2 (ProductName IN VARCHAR2
                      , ProcessSpecName IN VARCHAR2
                      , ProcessSpecRevision IN VARCHAR2
                      , ProcessSpecObjectCategory IN VARCHAR2
                      , ProcessSpecObjectType IN VARCHAR2
                      , SchedulingRuleName IN VARCHAR2
                      , ScheduleQty IN VARCHAR2
                      , TFI IN VARCHAR2
                      , FlowCode IN VARCHAR2
                      , ConCode IN VARCHAR2
                      , Priority IN VARCHAR2
                      , LotIds IN OUT VARCHAR2)
;
--===========================================================================
-- End of package specification
--===========================================================================
END;
/
CREATE OR REPLACE PACKAGE BODY LotSchedule AS

--=====================
-- Package information
--=====================
FUNCTION Version RETURN VARCHAR2 IS BEGIN RETURN ('3.4.0001'); END;

--=====================
-- GetLotIds
--=====================
FUNCTION GetLotIds (ProductName IN VARCHAR2
                    , ProcessSpecName IN VARCHAR2
                    , ProcessSpecRevision IN VARCHAR2
                    , ProcessSpecObjectCategory IN VARCHAR2
                    , ProcessSpecObjectType IN VARCHAR2
                    , SchedulingRuleName IN VARCHAR2
                    , ScheduleQty IN VARCHAR2
                    , TFI IN VARCHAR2
                    , FlowCode IN VARCHAR2
                    , ConCode IN VARCHAR2
                    , Priority IN VARCHAR2)
RETURN VARCHAR2 IS
    --=====================
    -- Local variables
    --=====================
    vLotIds VARCHAR2(1000);
    --=====================
    -- Pragma Declarations
    --=====================
    eExit EXCEPTION;
    PRAGMA EXCEPTION_INIT (eExit, -20000);
    eAppError EXCEPTION;
    PRAGMA EXCEPTION_INIT (eAppError, -20005);
BEGIN
    --=====================
    -- Call the procedure
    --=====================
    GetLotIds2 (ProductName
                , ProcessSpecName
                , ProcessSpecRevision
                , ProcessSpecObjectCategory
                , ProcessSpecObjectType
                , SchedulingRuleName
                , ScheduleQty
                , TFI
                , FlowCode
                , ConCode
                , Priority
                , vLotIds);
    --=====================
    -- Return success
    --=====================
    RETURN ('0 ' || vLotIds);
EXCEPTION
    WHEN eExit THEN
        RETURN ('LotSchedule.GetLotIds->Exited prematurely');
    WHEN eAppError THEN
        RETURN (REPLACE(SQLERRM, 'ORA-20005: '));
    WHEN OTHERS THEN
        RETURN ('LotSchedule.GetLotIds->' || SQLERRM);
END GetLotIds;

--=====================
-- GetLotIds2
--=====================
PROCEDURE GetLotIds2 (ProductName IN VARCHAR2
                      , ProcessSpecName IN VARCHAR2
                      , ProcessSpecRevision IN VARCHAR2
                      , ProcessSpecObjectCategory IN VARCHAR2
                      , ProcessSpecObjectType IN VARCHAR2
                      , SchedulingRuleName IN VARCHAR2
                      , ScheduleQty IN VARCHAR2
                      , TFI IN VARCHAR2
                      , FlowCode IN VARCHAR2
                      , ConCode IN VARCHAR2
                      , Priority IN VARCHAR2
                      , LotIds IN OUT VARCHAR2)
IS
    --=====================
    -- Input parameter variables
    --=====================

    --=====================
    -- Local variables
    --=====================
    vLotIds VARCHAR2(1000);
    --=====================
    -- Pragma Declarations
    --=====================
    eExit EXCEPTION;
    PRAGMA EXCEPTION_INIT (eExit, -20000);
    eAppError EXCEPTION;
    PRAGMA EXCEPTION_INIT (eAppError, -20005);
BEGIN
    --=====================
    -- Determine which scheduling rules procedure to call
    -- E.g.
    --    IF (ProcessSpecObjectType = 'ASPEN') AND (SchedulingRuleName = 'UTS') THEN
    --        GetAspenUTSLotIds(ProductName, ...);
    --    ELSIF (ProcessSpecObjectType = 'ASPEN') AND (SchedulingRuleName = 'FGS') THEN
    --        GetAspenFGSLotIds(ProductName, ...);
    --    ELSIF
    --        ...
    --    ENDIF;
    --=====================

    --=====================
    -- Return the lot ids
    --=====================
    LotIds := vLotIds;
EXCEPTION
    WHEN eAppError THEN
        RAISE_APPLICATION_ERROR (-20005, SQLERRM);
    WHEN OTHERS THEN
        RAISE_APPLICATION_ERROR (-20005, 'LotSchedule.GetLotIds2->' || SQLERRM);
END GetLotIds2;

--===========================================================================
-- End of package body
--===========================================================================
END;
/
